package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import Gui.Terminal;
import Model.Supplier;

/**
 * @author Patrick
 */


public class DBSupplier {
	
	private final String supplierID = "SupplierID";
	private final String name = "Name";
	private final String address = "Address";
	private final String country = "Country";
	private final String phoneNum = "PhoneNo";
	private final String email = "Email";
	
	private final String selectAll = String.format("SELECT %s, %s, %s, %s, %s, %s FROM Supplier", 
			supplierID, name, address, country, phoneNum, email);
	
	private DBConnection conn;
	private Connection con;
	
	public DBSupplier()
	{
		conn = DBConnection.getInstance();
		con = DBConnection.getInstance().getDBcon();
	}
	
	public boolean updateSupplier(String ID, String Name, String Address, String Country, String PhoneNum, String Email)
	{
		boolean control = false;
		String query = String.format("Update supplier set name = '%s', address = '%s', country = '%s', " +
				"phoneNo = '%s', email = '%s' where supplierID = '" + ID + "'", Name, Address, Country, PhoneNum, Email);
		try{
			Statement stmt = con.createStatement();
			Terminal.say(query);
			stmt.setQueryTimeout(5);
			stmt.execute(query);
			stmt.close();
			control = true;
		}
		catch(Exception ex)
		{
			System.out.print("updating");
		}
		return control;
		
	}
	
	public boolean deleteSupplier(String ID)
	{
		boolean Control = false;
		String query = String.format("DELETE FROM supplier Where SupplierID = '" + ID + "'");
		try{
			Statement stmt = con.createStatement();
			Terminal.say(query);
			stmt.setQueryTimeout(5);
			stmt.execute(query);
			stmt.close();
			Control = true;
		}
		catch(Exception ex)
		{
			System.out.print("failure in deletion");
		}
		return Control;
	}
	
	public boolean createSupplier(Supplier sup)
	{	
		
		String Name = sup.getName();
		String Address = sup.getAddress();
		String Country = sup.getCountry();
		String PhoneNo = sup.getPhoneNum();
		String Email = sup.getEmail();
		String ID = sup.getSupplierID();
		boolean success = true;
		
		String Query = String.format("INSERT INTO Supplier VALUES ('%s', '%s', '%s', '%s', '%s', '%s')", ID, Name, Address, Country, PhoneNo, Email);
		
		try{ 
			Statement stmt = con.createStatement();
			Terminal.say(Query);
	        stmt.setQueryTimeout(5);
	     	stmt.execute(Query);
	        stmt.close();
	    }
	    catch(SQLException ex){
	        Terminal.say("Supplier not created, line 70 DBSupplier", ex);
	    }
		return(success);
	}
	
	public Supplier getSupplier(String _suppID)
	{
		Supplier result = null;
		
				java.sql.Statement stmt = null;
				try {
					stmt = conn.getDBcon().createStatement();
				}
				catch(SQLException sE) {
					Terminal.say("An error occured during statement creation. \n Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				String condition = SelectWhere(supplierID + " = " + _suppID);
				try {
					stmt.execute(condition);
				}
				catch(SQLException sE) {
					Terminal.say("An error occured executing the statement.");
					Terminal.say(condition);
				    Terminal.say("Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Get result
				ResultSet resSet = null;
				try {
					resSet = stmt.getResultSet();
				}
				catch(SQLException sE) {
					Terminal.say("The resultSet could not be retrieved. \n Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Build the products
				if(resSet != null) {
					result = BuildSupplier(resSet);
				}
				else {
					Terminal.say("No result was found");
				}
				
				//Close connection
				if(stmt != null) {
					try {
						stmt.close();
					}
					catch(SQLException sE) {
						Terminal.say("Stack trace: ");
						Terminal.say(sE.getStackTrace().toString());
					}
				}
				
				return result;
		
	}
	
	
	public ArrayList<Supplier> getAllSuppliers()
	{
		ArrayList<Supplier> allSuppliers = new ArrayList<Supplier>(); 
		
		//Create statement
				java.sql.Statement stmt = null;
				try {
					stmt = conn.getDBcon().createStatement();
				}
				catch(SQLException sE) {
					Terminal.say("Stack Trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Execute statement
				try {
					stmt.execute(selectAll);
				}
				catch(SQLException sE) {
					Terminal.say("Stack Trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Retrieve results
				ResultSet resSet = null;
				try {
					resSet = stmt.getResultSet();
				}
				catch(SQLException sE) {
					Terminal.say("Stack Trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Build an Product object for each result
				try {
					do {
						allSuppliers.add(BuildSupplier(resSet));
					} while(resSet.next());
				}
				catch(SQLException sE) {
					Terminal.say("Stack Trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Close connection
				if(stmt != null) {
					try {
						stmt.close();
					}
					catch(SQLException sE) {
						Terminal.say("Stack Trace: ");
						Terminal.say(sE.getStackTrace().toString());
					}
				}
				
				return allSuppliers;
	}
	public String getHighestNo()
	{
		Statement stmt = null;
		
		try {
			stmt = conn.getDBcon().createStatement();
		} catch (SQLException e) {
			Terminal.say("Could not create statement to delete invoice to DB", e);
		}
		
		try {
			stmt.execute(String.format("SELECT MAX(CAST(%s AS Int)) AS Res FROM Supplier", this.supplierID));
		} catch (SQLException e) {
			Terminal.say("Could not find highest SupplierID no", e);
		}
		
		ResultSet resSet = null;
		
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e) {
			Terminal.say("Could not get ResultSet from getHighestNo", e);
		}
		
		String result = null;
		try {
			resSet.next();
			int hiNo = resSet.getInt("Res");
			result = hiNo + "";
		}
		catch(Exception e) {
			Terminal.say("En Error occured in getHighestNo()", e);
		}
		try
		{
			stmt.close();
		}
		catch(Exception e)
		{
			Terminal.say("could not close");
		}
		
		return result;
	}
	
//******************************************************************************************************************************************//	
	private String SelectWhere(String condition)
	{
		return selectAll + " where " + condition;
	}
	
	private Supplier BuildSupplier(ResultSet resSet)
	{
		try {
			resSet.next();
			String SuppliID = resSet.getString(supplierID);
			String name = resSet.getString(this.name);
			String Addr = resSet.getString(address);
			String Countr = resSet.getString(country);
			String PhNum = resSet.getString(phoneNum);
			String Mail = resSet.getString(email);
			
			return new Supplier(SuppliID, name, Addr, Countr, PhNum, Mail);
		}
		catch(SQLException sE)
		{
			Terminal.say("Could not build Supplier Object \n Stack trace: ");
			Terminal.say(sE.getStackTrace().toString());
			
			return null;
		}
	}
}
